package com.rr.mymoney.dao.impl.hibernate;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.rr.mymoney.dao.ReportsDao;
import com.rr.mymoney.model.Account;
import com.rr.mymoney.model.Category;
import com.rr.mymoney.model.Currency;
import com.rr.mymoney.model.util.ReportItem;
import com.rr.mymoney.utils.DateUtils;

@Repository
public class ReportsDaoImpl extends AbstractDao implements ReportsDao {

    private static transient Logger log = Logger.getLogger(ReportsDaoImpl.class);

    public List<ReportItem> getStatisticsData(List<Long> userIds, int month, int year, boolean groupByYear, boolean showAllMonths,
	    boolean showAllYears, boolean groupByAccount, boolean groupByCategory, Long accountId, Long categoryId, String currencyId, boolean showIgnored) {
	StringBuilder sb = new StringBuilder();
	sb.append(" select year(t.date), ");
	if (groupByYear) {
	    sb.append(" year(t.date), ");
	} else {
	    sb.append(" month(t.date), ");
	}
	sb.append(" t.account.currency ");
	sb.append(" ,sum(t.value) as balance");
	sb.append(" ,sum(case when t.value < 0 then t.value else 0 end) as outgoings ");
	sb.append(" ,sum(case when t.value > 0 then t.value else 0 end) as income ");
	if (groupByAccount) {
	    sb.append(", t.account.id, t.account.name");
	}
	if (groupByCategory) {
	    sb.append(", coalesce(c.id, -1) as categoryId ");
	    sb.append(", coalesce(c.name, 'No category') as categoryName ");
	}

	sb.append(" from Transaction as t ");
	sb.append(" left outer join t.category as c");

	sb.append(" where 1 = 1 ");
	if(!showIgnored) {
	    sb.append(" and t.ignoreInReports = false ");
	}
	sb.append(" and t.user.id in (:userIds) ");
	if (!showAllYears) {
	    if (month != 0 && year != 0) {
		sb.append(" and t.date >= :startDate ");
		sb.append(" and t.date <= :endDate ");
	    }
	}
	if (accountId != null) {
	    sb.append(" and t.account.id = :accountId ");
	}
	if (categoryId != null) {
	    sb.append(" and c.id = :categoryId ");
	}
	if (currencyId != null) {
	    sb.append(" and t.account.currency.id = :currencyId");
	}

	sb.append(" group by year(t.date), ");

	if (!groupByYear) {
	    sb.append(" month(t.date), ");
	}
	sb.append(" t.account.currency ");
	if (groupByAccount) {
	    sb.append(", t.account.name");
	}
	if (groupByCategory) {
	    sb.append(", coalesce(c.name, 'No category')");
	}
	sb.append(" order by year(t.date) desc, ");
	if (!groupByYear) {
	    sb.append(" month(t.date) desc, ");
	}
	sb.append(" t.account.currency, sum(case when t.value < 0 then t.value else 0 end), sum(case when t.value > 0 then t.value else 0 end) ");
	if (groupByAccount) {
	    sb.append(", t.account.name");
	}
	if (groupByCategory) {
	    sb.append(", coalesce(c.name, 'No category')");
	}

	Query query = getCurrentSession().createQuery(sb.toString());

	query.setParameterList("userIds", userIds);

	if (!showAllYears) {
	    if (month != 0 && year != 0) {
		if (groupByYear || showAllMonths) {
		    query.setTimestamp("startDate", DateUtils.getStartDate(year, 1));
		    query.setTimestamp("endDate", DateUtils.getEndDate(year, 12));
		} else {
		    query.setTimestamp("startDate", DateUtils.getStartDate(year, month));
		    query.setTimestamp("endDate", DateUtils.getEndDate(year, month));
		}
	    }
	}
	if (accountId != null) {
	    query.setLong("accountId", accountId);
	}
	if (categoryId != null) {
	    query.setLong("categoryId", categoryId);
	}
	if (currencyId != null) {
	    query.setString("currencyId", currencyId);
	}

	List<ReportItem> rtn = new ArrayList<ReportItem>();
	@SuppressWarnings("unchecked")
	List<Object[]> list = query.list();
	list.size();
	SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
	for (Object[] obj : list) {
	    ReportItem ri = new ReportItem();
	    try {
		if (groupByYear) {
		    ri.setDate(dateFormat.parse("5.1." + obj[0]));
		} else {
		    ri.setDate(dateFormat.parse("1." + obj[1] + "." + obj[0]));
		}
	    } catch (ParseException e) {
		log.error("Error parsing date: ", e);
	    }
	    ri.setCurrency((Currency) obj[2]);
	    ri.setBalance(((Double) obj[3]).floatValue());
	    ri.setOutgoings(((Double) obj[4]).floatValue());
	    ri.setIncome(((Double) obj[5]).floatValue());
	    if (groupByAccount) {
		Account account = new Account();
		account.setId((Long) obj[6]);
		account.setName((String) obj[7]);
		ri.setAccount(account);

		if (groupByCategory) {
		    Category category = new Category();
		    category.setId((Long) obj[8]);
		    category.setName((String) obj[9]);
		    ri.setCategory(category);
		}
	    } else {
		if (groupByCategory) {
		    Category category = new Category();
		    category.setId((Long) obj[6]);
		    category.setName((String) obj[7]);
		    ri.setCategory(category);
		}
	    }

	    rtn.add(ri);
	}
	return rtn;
    }
}
